from sqlalchemy import Column, Integer, String, DateTime, Enum as SQLEnum, ForeignKey
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Enum as SQLEnum, Boolean
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from datetime import datetime

from models import TodoStatus, TodoPriority, UserRole
from database import Base

class UserDB(Base):
    """
    用户数据库模型
    """
    __tablename__ = "users"
    
    # 主键
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    
    # 用户信息
    username = Column(String(50), unique=True, nullable=False, index=True)
    password_hash = Column(String(255), nullable=False)
    role = Column(SQLEnum(UserRole), nullable=False, default=UserRole.USER, index=True)  # 添加角色字段
    
    # 时间戳
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    
    # 关系
    todos = relationship("TodoDB", back_populates="user", cascade="all, delete-orphan")
    
    def __repr__(self):
        return f"<UserDB(id={self.id}, username='{self.username}')>"
    
    def to_dict(self):
        """
        转换为字典格式
        """
        return {
            'id': self.id,
            'username': self.username,
            'role': self.role.value if self.role else None,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

class TodoDB(Base):
    """
    待办事项数据库模型
    """
    __tablename__ = "todos"
    
    # 主键
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    
    # 用户关联
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    
    # 基本信息
    title = Column(String(255), nullable=False, index=True)
    description = Column(String(1000), nullable=True)
    
    # 状态和优先级
    status = Column(SQLEnum(TodoStatus), nullable=False, default=TodoStatus.PENDING)
    priority = Column(SQLEnum(TodoPriority), nullable=False, default=TodoPriority.MEDIUM)
    
    # 排序字段
    order = Column(Integer, nullable=False, default=0, index=True)
    
    # 管理员分配标识
    assigned_by_admin = Column(Boolean, nullable=False, default=False, index=True)
    
    # 时间戳
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    
    # 关系
    user = relationship("UserDB", back_populates="todos")
    
    def __repr__(self):
        return f"<TodoDB(id={self.id}, title='{self.title}', status='{self.status}')>"
    
    def to_dict(self):
        """
        转换为字典格式
        """
        return {
            'id': self.id,
            'title': self.title,
            'description': self.description,
            'status': self.status.value if self.status else None,
            'priority': self.priority.value if self.priority else None,
            'order': self.order,
            'assigned_by_admin': self.assigned_by_admin,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

class NotificationDB(Base):
    """
    通知数据库模型
    """
    __tablename__ = "notifications"
    
    # 主键
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    
    # 用户关联
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    
    # 通知内容
    title = Column(String(255), nullable=False)
    message = Column(String(1000), nullable=False)
    
    # 通知状态
    is_read = Column(Boolean, nullable=False, default=False, index=True)
    
    # 时间戳
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    
    # 关系
    user = relationship("UserDB")
    
    def __repr__(self):
        return f"<NotificationDB(id={self.id}, title='{self.title}', is_read={self.is_read})>"
    
    def to_dict(self):
        """
        转换为字典格式
        """
        return {
            'id': self.id,
            'user_id': self.user_id,
            'title': self.title,
            'message': self.message,
            'is_read': self.is_read,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }